﻿using LinkSql;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using hospital_registration_system.Entity;

namespace hospital_registration_system.DAL
{
    public class DoctorDao
    {
        private string tableName = "doctor";//表名

        MySqlDataAdapter mySqlDataAdapter;

        /// <summary>
        /// 医生登录
        /// </summary>
        /// <param name="tel">电话号</param>
        /// <param name="key">密码</param>
        /// <returns>医生对象</returns>
        public Doctor Login(string tel, string key)
        {
            Doctor doctor = null;
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                connection.Open();
                string sql = "select * from doctor where telephone='" + tel + "'and doctor_password='" + key + "'";
                mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    doctor = new Doctor(
                    int.Parse(dataTable.Rows[0]["id"].ToString()),
                    dataTable.Rows[0]["doctor_name"].ToString(),
                    dataTable.Rows[0]["nickname"].ToString(),
                    dataTable.Rows[0]["sex"].ToString(),
                    int.Parse(dataTable.Rows[0]["age"].ToString()),
                    dataTable.Rows[0]["email"].ToString(),
                    dataTable.Rows[0]["telephone"].ToString(),
                    dataTable.Rows[0]["address"].ToString(),
                    dataTable.Rows[0]["doctor_password"].ToString(),
                    int.Parse(dataTable.Rows[0]["department_id"].ToString()),
                    dataTable.Rows[0]["created_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["created_time"]),
                    dataTable.Rows[0]["updated_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["updated_time"]),
                    int.Parse(dataTable.Rows[0]["state"].ToString())
                                    );

                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {

                connection.Close();
            }
            return doctor;
        }

        /// <summary>
        /// 通过DOctor对象新增加信息
        /// </summary>
        /// <param name="doctor"></param>
        /// <returns></returns>
        public bool insert(Doctor doctor)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                string sql = "insert into doctor(nickname,doctor_name,sex,age,telephone,email,address,doctor_password,department_id,created_by,created_time,updated_by,updated_time,state) values("
                    + "'" + doctor.Nickname + "'" + ","
                    + "'" + doctor.Doctor_name + "'" + ","
                    + "'" + doctor.Sex + "'" + ","
                    + "'" + doctor.Age + "'" + ","
                    + "'" + doctor.Telephone + "'" + ","
                    + "'" + doctor.Email + "'" + ","
                    + "'" + doctor.Address + "'" + ","
                    + "'" + doctor.Doctor_password + "'" + ","
                    + "'" + doctor.Department_id + "'" + ","
                    + "'" + doctor.CREATED_BY1 + "'" + ","
                    + "'" + doctor.CREATED_TIME1 + "'" + ","
                    + "'" + doctor.UPDATED_BY1 + "'" + ","
                    + "'" + doctor.UPDATED_TIME1 + "'" + ","
                    + "'" + doctor.State + "'"
                    + ")";
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();


            }
            catch (MySqlException)
            {

                return false;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        /// <summary>
        /// 通过传入的id删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delete(int id)
        {
            return MySQLUtils.delete(tableName,id);
        }

        /// <summary>
        /// 通过传入的Doctor对象修改信息
        /// </summary>
        /// <param name="doctor"></param>
        /// <returns></returns>
        public bool update(Doctor doctor)
        {

            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "update doctor set "
                + "nickname='" + doctor.Nickname + "',"
                + "doctor_name='" + doctor.Doctor_name + "'," +
                "sex='" + doctor.Sex + "'," +
                "age='" + doctor.Age + "'," +
                "telephone='" + doctor.Telephone + "'," +
                "email='" + doctor.Email + "'," +
                "address='" + doctor.Address + "'," +
                "doctor_password='" + doctor.Doctor_password + "'," +
                "department_id='" + doctor.Department_id + "'," +
                "created_by='" + doctor.CREATED_BY1 + "'," +
                "created_time='" + doctor.CREATED_TIME1 + "'," +
                "updated_by='" + doctor.UPDATED_BY1 + "'," +
                "updated_time='" + doctor.UPDATED_TIME1 + "'," +
                "state='" + doctor.State + "' " +
                "where id = " + doctor.Id
                ;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;

        }


        /// <summary>
        /// 通过id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns>一个doctor</returns>
        public Doctor selectById(int id)
        {
            return GetDoctorsByDataTable(MySQLUtils.selectById(tableName, id))[0];
        }


        /// <summary>
        /// 查询所有医生
        /// </summary>
        /// <returns></returns>
        public List<Doctor> selectAll()
        {
            return GetDoctorsByDataTable(MySQLUtils.selectAll(tableName));
        }

        /// <summary>
        /// 将dataTable转化为doctor
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public List<Doctor> GetDoctorsByDataTable(DataTable dataTable)
        {
            List<Doctor> doctorList = new List<Doctor>();
            foreach (DataRow row in dataTable.Rows)
            {
                Doctor doctor = new Doctor(
                    int.Parse(row["id"].ToString()),
                    row["doctor_name"].ToString(),
                    row["nickname"].ToString(),
                    row["sex"].ToString(),
                    int.Parse(row["age"].ToString()),
                    row["email"].ToString(),
                    row["telephone"].ToString(),
                    row["address"].ToString(),
                    row["doctor_password"].ToString(),
                    int.Parse(row["department_id"].ToString()),
                    row["created_by"].ToString(),
                    (DateTime)(row["created_time"]),
                    row["updated_by"].ToString(),
                    (DateTime)(row["updated_time"]),
                    int.Parse(row["state"].ToString())
                );
                doctorList.Add(doctor);
            }
            return doctorList;
        }


        public List<Doctor> selectByMap(Dictionary<string,Object> map)
        {
            return GetDoctorsByDataTable(MySQLUtils.selectByMap(tableName, map));
        }

    }
}
